/**
 * 
 */
package com.pub.dbo.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.log4j.Logger;

import com.pub.db.DBConnectionManager;
import com.pub.dbo.dao.ModuleRunLogDAO;
import com.pub.dbo.domain.ModuleRunLog;
import com.pub.util.DateUtils;

/**
 * @author iling
 *
 */
public class ModuleRunLogImpl implements ModuleRunLogDAO
{
	private static Logger logger;
	private DBConnectionManager dbcm;
	
	private String DBTag;
	
	static
	{
		logger = Logger.getLogger(ModuleRunLogImpl.class.getName());
	}
	
	/**
	 * 
	 */
	public ModuleRunLogImpl(String DBTag) 
	{
		this.DBTag = DBTag;
		
		dbcm	= DBConnectionManager.getInstance(ModuleRunLogImpl.class.getName());
	}

	public int insert(ModuleRunLog module)
	{
		int id = 0;
		
		Connection conn = null;
		Statement sm 	= null;
		ResultSet rs	= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		
		sbSql.append("INSERT INTO ModuleRunLog SET CreatedTime = UTC_TIMESTAMP(), ModifiedTime = UTC_TIMESTAMP(), Operator='auto', Module='");
		sbSql.append(module.getModule());
		
		sbSql.append("', orders = ");
		
		sbSql.append(module.getOrder());

		sbSql.append(", CountryID = '");
		sbSql.append(module.getCountryID());
		
		sbSql.append("', BatchID = ");
		sbSql.append(module.getBatchID());

		sbSql.append(", Status = '");
		sbSql.append(module.getStatus());
		
		if(module.getCommments() != null){
			sbSql.append("', Commments = '");
			sbSql.append(module.getCommments());
		}
		
		sbSql.append("'");

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			sm.executeUpdate(sbSql.toString(), Statement.RETURN_GENERATED_KEYS);
			rs = sm.getGeneratedKeys();
			if(rs.next()){
				id = rs.getInt(1);
				
				module.setID(id);
			}
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (rs != null){
	        	try{
	        		rs.close();
				} catch (Exception e){
				}
	        }
			
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("insert method return id value : " + id);
		
		return id;
	}
	
	public int update(ModuleRunLog module)
	{
		int updateCount = 0;
		
		Connection conn = null;
		Statement sm 	= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		
		sbSql.append("INSERT INTO ModuleRunLog SET CreatedTime = UTC_TIMESTAMP(), ModifiedTime = UTC_TIMESTAMP(), Operator='auto', Module='");
		sbSql.append(module.getModule());
		
		sbSql.append("', orders = ");
		sbSql.append(module.getOrder());
		
		sbSql.append(", id = ");
		sbSql.append(module.getID());

		sbSql.append(", CountryID = '");
		sbSql.append(module.getCountryID());
		
		sbSql.append("', BatchID = ");
		sbSql.append(module.getBatchID());
		
		sbSql.append(", Status = '");
		sbSql.append(module.getStatus());
		
		if(module.getCommments() != null){
			sbSql.append("', Commments = '");
			sbSql.append(module.getCommments());
		}
		
		sbSql.append("' ON DUPLICATE KEY UPDATE Commments=VALUES(Commments), Status=VALUES(Status), orders=VALUES(orders), Module=VALUES(Module), Operator=VALUES(Operator), ModifiedTime=VALUES(ModifiedTime)");

		try{
			conn 	= dbcm.getConnection(DBTag);
			sm 		= conn.createStatement();
			
			updateCount = sm.executeUpdate(sbSql.toString());
		} catch (Exception e){
			logger.error("SQL Error : " + sbSql.toString() + ", Exception : " + e.getMessage());
		} finally {
			if (sm != null){
	        	try{
	        		sm.close();
				} catch (Exception e){
				}
	        }
			
			if (conn != null)
				dbcm.freeConnection(DBTag, conn);
		}
		
		logger.debug("update method return update count : " + updateCount);
		
		return updateCount;
	}
	
	@Override
	public ModuleRunLog getModuleRunLog(int id) 
	{
		ModuleRunLog obReturn = null;
		
		Connection 	conn	= null;
		Statement	sm		= null;
		ResultSet	rs		= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		sbSql.append("SELECT Operator, orders, Commments, Status, CreatedTime, ModifiedTime, CountryID, BatchID FROM ModuleRunLog WHERE ID ='");
		sbSql.append(id);
		sbSql.append("'");
		
        
		try {
			conn 	= dbcm.getConnection(DBTag);
			sm		= conn.createStatement();
			rs		= sm.executeQuery(sbSql.toString());
			if(rs.next()){
				obReturn = new ModuleRunLog();
				
				obReturn.setID(id);
				
				obReturn.setModule(rs.getString(1));
				
				obReturn.setOperator(rs.getString(2));
				obReturn.setOrder(rs.getInt(3));
				
				obReturn.setCommments(rs.getString(4));
				obReturn.setStatus(rs.getString(5));
				
				obReturn.setCreatedTime(rs.getString(6));
				obReturn.setModifiedTime(rs.getString(7));
				
				obReturn.setCountryID(rs.getString(8));
				obReturn.setBatchID(rs.getInt(9));
			}
		} catch (Exception e) {
			logger.error("Error SQL : " + sbSql.toString() + ", Exception : " + e.getMessage());
		}
		     
		return obReturn;
	}

	@Override
	public ModuleRunLog getModuleRunLog(String module) 
	{
		ModuleRunLog obReturn = null;
		
		Connection 	conn	= null;
		Statement	sm		= null;
		ResultSet	rs		= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		sbSql.append("SELECT ID, Operator, orders, Commments, Status, CreatedTime, ModifiedTime, CountryID, BatchID FROM ModuleRunLog WHERE Module='");
		sbSql.append(module);
		sbSql.append("'");
		
        
		try {
			conn 	= dbcm.getConnection(DBTag);
			sm		= conn.createStatement();
			rs		= sm.executeQuery(sbSql.toString());
			if(rs.next()){
				obReturn = new ModuleRunLog();
				
				obReturn.setModule(module);
				
				obReturn.setID(rs.getInt(1));
				obReturn.setOperator(rs.getString(2));
				obReturn.setOrder(rs.getInt(3));
				
				obReturn.setCommments(rs.getString(4));
				obReturn.setStatus(rs.getString(5));
				
				obReturn.setCreatedTime(rs.getString(6));
				obReturn.setModifiedTime(rs.getString(7));
				
				obReturn.setCountryID(rs.getString(8));
				obReturn.setBatchID(rs.getInt(9));
			}
		} catch (Exception e) {
			logger.error("Error SQL : " + sbSql.toString() + ", Exception : " + e.getMessage());
		}
		     
		return obReturn;
	}

	@Override
	public ModuleRunLog getTodayNewestModuleRunLog(String module) 
	{
		ModuleRunLog obReturn = null;
		
		Connection 	conn	= null;
		Statement	sm		= null;
		ResultSet	rs		= null;
		
		StringBuilder sbSql = new StringBuilder(100);
		sbSql.append("SELECT ID, Operator, orders, Commments, Status, CreatedTime, ModifiedTime, CountryID, BatchID FROM ModuleRunLog WHERE Module='");
		sbSql.append(module);
		sbSql.append("' AND CreatedTime like '");
		sbSql.append(DateUtils.getNowDate());
		sbSql.append("%' ORDER BY ID DESC LIMIT 1");
        
		try {
			conn 	= dbcm.getConnection(DBTag);
			sm		= conn.createStatement();
			rs		= sm.executeQuery(sbSql.toString());
			if(rs.next()){
				obReturn = new ModuleRunLog();
				
				obReturn.setModule(module);
				
				obReturn.setID(rs.getInt(1));
				obReturn.setOperator(rs.getString(2));
				obReturn.setOrder(rs.getInt(3));
				
				obReturn.setCommments(rs.getString(4));
				obReturn.setStatus(rs.getString(5));
				
				obReturn.setCreatedTime(rs.getString(6));
				obReturn.setModifiedTime(rs.getString(7));
				
				obReturn.setCountryID(rs.getString(8));
				obReturn.setBatchID(rs.getInt(9));
			}
		} catch (Exception e) {
			logger.error("Error SQL : " + sbSql.toString() + ", Exception : " + e.getMessage());
		}
		     
		return obReturn;
	}
	
	@Override
	public void close() 
	{
		dbcm.release(ModuleRunLogImpl.class.getName());
	}

}
